﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Data;

using NetworkAssetManager.General;
using NetworkAssetManager.DataAccess;
using NetworkAssetManager.Entity;

namespace NetworkAssetManager.DataAccess
{
    public class CredentialDB:BaseDB
    {
        public int CreateTable()
        {
            throw new System.NotImplementedException();
        }

        public int InsertCredential(EntCredential credential)
        {
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = @"
                            INSERT INTO Credential
                                (Username, Password, CredentialName)
                                 VALUES (?, ?, ?)";
                        command.Parameters.Add(new SqlCeParameter("Username",
                            SqlDbType.NVarChar, 255)).Value = credential.Username;
                        command.Parameters.Add(new SqlCeParameter("Password",
                            SqlDbType.NVarChar, 255)).Value = credential.Password;
                        command.Parameters.Add(new SqlCeParameter("CredentialName",
                            SqlDbType.NVarChar, 255)).Value = credential.CredentialName;
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
            catch (Exception){}
            return 0; 
        }

        public int DeleteCredentials(EntCredential cred)
        {
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = @"
                            DELETE FROM [Credential] where CredentialID = ?";

                        command.Parameters.Add(new SqlCeParameter("CredentialID",
                            SqlDbType.Int, 255)).Value = cred.CredentialID;
                        
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
            catch (Exception) { }
            return 0;
        }


        public int DeleteAllCredentials()
        {
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = @"
                            DELETE from [Credential];";

                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
            }
            catch (Exception) { }
            return 0;
        }


        public List<EntCredential> GetAllCredentials()
        {
            List<EntCredential> list = new List<EntCredential>();

            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    command.CommandText = @"
                          SELECT * FROM [Credential] ";

                    using (SqlCeDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(DataReaderToObject(reader));
                        }
                    }


                }
            }
            catch (Exception ){}
            return list;
        }

        public EntCredential GetCredential(int credentialID)
        {
            EntCredential credentials = null; 

            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {

                    command.CommandText = @"
                          SELECT * FROM [Credential] where CredentialID = ?";

                    command.Parameters.Add(new SqlCeParameter("CredentialID",
                        SqlDbType.Int, 255)).Value = credentialID;

                    using (SqlCeDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            credentials = DataReaderToObject(reader);
                        }
                    }
                }
            }
            catch (Exception) { }
            return credentials;
        }

        public DBCode UpdateCredential(EntCredential cred)
        {
            DBCode retCode = DBCode.Ok;
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = @"
                            UPDATE [Credential] SET Username = ?, Password = ?, CredentialName = ? WHERE CredentialID = ?";
                        command.Parameters.Add(new SqlCeParameter("Username",
                            SqlDbType.NVarChar, 255)).Value = cred.Username;
                        command.Parameters.Add(new SqlCeParameter("Password",
                            SqlDbType.NVarChar, 255)).Value = cred.Password;
                        command.Parameters.Add(new SqlCeParameter("CredentialName",
                            SqlDbType.NVarChar, 255)).Value = cred.CredentialName;
                        command.Parameters.Add(new SqlCeParameter("CredentialID",
                            SqlDbType.Int, 4)).Value = cred.CredentialID;

                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    retCode = DBCode.Ok;
                }
            }
            catch (Exception)
            {
                retCode = DBCode.Error;
            }
            return retCode; 

        }

        public void UpdateCredentials (List<EntCredential> lstCredentials)
        {
            foreach (EntCredential cred in lstCredentials)
            {
                switch (cred.Operation)
                {
                    case DBOperations.Insert:
                        InsertCredential(cred); 
                        break; 
                    case DBOperations.Delete:
                        DeleteCredentials(cred); 
                        break; 
                    case DBOperations.Update:
                        UpdateCredential(cred);
                        break; 
                }
            }
        }

        private EntCredential DataReaderToObject(SqlCeDataReader reader)
        {
            EntCredential credential = new EntCredential();
            credential.CredentialID = reader.GetInt32(reader.GetOrdinal("CredentialID")); 
            credential.Username = reader.GetString(reader.GetOrdinal("Username"));
            credential.Password = reader.GetString(reader.GetOrdinal("Password"));
            credential.CredentialName = reader.GetString(reader.GetOrdinal("CredentialName"));
            return credential;
        }
    }
}
